<?php
	class ClassesModel extends CI_Model {
		// 表名
		static $gradeTable = "school_grade";
		static $classTable = "school_class";
		static $classroomTable = "school_classroom";
		static $scheduleTable = "school_schedule";
		static $scheduleLessonTable = "school_schedule_lesson";
		static $brandTable = "school_brand";
		static $brandCdkeyTable = "school_brand_cdkey";
		static $signTable = "school_student_sign";
		static $signTempTable = "school_student_sign_temp";
		static $defaultGradeTable = "school_grade_default";
		static $gradePeriodTable = "school_grade_period";

	    public function __construct() {
	        parent::__construct();
	    }

	    // 获取年级列表
	    public function get_grade_list($params = ""){
	    	$where = "1 AND 1";
	    	// 分页
	    	if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
	    	// 读取指定列
	    	if(isset($params['field'])) $this->db->select($params['field']);
	    	// 支持‘功能课室’，school_id和campus_id都为空
	    	if(isset($params['schoolId'])) $where .= " AND (`school_id`='{$params['schoolId']}' OR `school_id`=0)";
	    	if(isset($params['campusId'])) $where .= " AND (`campus_id`='{$params['campusId']}' OR `campus_id`=0)";
	    	// 删除状态
			if(isset($params['isDelete'])) $where .= " AND `is_delete`='{$params['isDelete']}'";
			// 是否有默认年级id
			if(isset($params['hasDefaultGradeId'])) $where .= " AND `default_grade_id` != 0";
			// 自动升级日期
			if(isset($params['autoUpgradeDate'])) $where .= " AND `auto_upgrade_date` = '{$params['autoUpgradeDate']}'";
			// 过滤已毕业年级
			if(isset($params['excludeGraduated'])) {
				$where .= " AND `grade_name` NOT LIKE '%毕业年级'";
			}
			// 默认年级id列表
			if(isset($params['defaultGradeIdSet'])) {
				$where .= " AND FIND_IN_SET(`default_grade_id`, '{$params['defaultGradeIdSet']}')";
			}

			// 筛选已毕业
			if(isset($params['isGraduated'])) {
				$where .= " AND `is_graduated` = 1";
			}

			// 默认排除已毕业
			if(!isset($params['includeGraduated'])) {
				$where .= " AND `is_graduated` = 0";
			}

			// 过滤已功能课室
			if(isset($params['excludeFunctional'])) {
				$where .= " AND `grade_name` != '功能课室'";
			}

			$this->db->where($where);
			// 默认按年级名称排序，功能课室排最后
			if(isset($params['orderBy'])) {
				$this->db->order_by($params['orderBy']['order'], $params['orderBy']['sort']);
			} else {
				$this->db->order_by('school_id', 'DESC');
				// 数值越大越靠前
				$this->db->order_by('sort_level', 'DESC');
				$this->db->order_by('grade_name', 'ASC');
			}
	    	// 是否只获取条数
	    	if(isset($params['getRowNumber'])) {
	    		$result = $this->db->count_all_results(self::$gradeTable);
	    	} else {
	    		$result = $this->db->get(self::$gradeTable)->result_array();	
	    	}
	    	return $result;
	    }
	    // 获取年级信息
	    public function get_grade_info($gradeId){
	    	$where = "`id`='{$gradeId}'";
	    	$gradeInfo = $this->db->where($where)->get(self::$gradeTable)->row_array();
	    	return $gradeInfo;
		}
	    // 通过名称获取年级信息
	    public function get_grade_info_by_name($gradeName, $schoolId, $campusId){
	    	$this->db->where(array(
				'grade_name' => $gradeName,
				'school_id' => $schoolId,
				'campus_id' => $campusId,
				'is_delete' => 0,
				'is_graduated' => 0
			));
	    	$gradeInfo = $this->db->get(self::$gradeTable)->row_array();
	    	return $gradeInfo;
		}
		// 获取功能课室年级
		public function get_grade_info_for_functional(){
			$this->db->where(array(
				'grade_name' => '功能课室',
				'school_id' => 0,
				'campus_id' => 0,
				'is_delete' => 0
			));
			$gradeInfo = $this->db->get(self::$gradeTable)->row_array();
			return $gradeInfo;
		}

		// 获取选修年级
		public function get_elective_grade_info_list($schoolId, $campusId){
			$electiveGradeType = GRADE_TYPE_ELECTIVE;
	    	$where = "`school_id`='{$schoolId}' AND `campus_id`='{$campusId}' AND `grade_type`='{$electiveGradeType}'";
			$gradeInfo = $this->db->where($where)->get(self::$gradeTable)->row_array();
			return $gradeInfo;
		}

	    // 根据年级id，获取班级id
	    public function get_class_list_by_grade($gradeId, $classType = 0){
			$where = "grade_id = '{$gradeId}' AND `is_delete` = 0";
			if($classType) {
				$where .= " AND `class_type` = '{$classType}'";
			}
	    	$classList = $this->db->where($where)->get(self::$classTable)->result_array();
	    	return $classList;
	    }
	    // 获取班级信息
	    public function get_class_info($classId){
			// $where = "`id`='{$classId}'";
			$where = "`is_delete`=0 AND `id`='{$classId}'";
	    	$classInfo = $this->db->where($where)->get(self::$classTable)->row_array();
	    	return $classInfo;
		}
		public function get_class_info_include_delete($classId){
			// $where = "`id`='{$classId}'";
			$where = "`id`='{$classId}'";
	    	$classInfo = $this->db->where($where)->get(self::$classTable)->row_array();
	    	return $classInfo;
		}
		// 通过学校和班级名获取班级信息
	    public function get_class_info_by_class_name($className, $schoolId){
			$this->db->where('school_id', $schoolId);
			$this->db->where('class_name', $className);
			$this->db->where('is_delete', 0);

			// 过滤已毕业
			$this->db->where('is_graduated', 0);

	    	$classInfo = $this->db->get(self::$classTable)->row_array();
	    	return $classInfo;
		}
	    // 获取班级列表
	    public function get_class_list($params = ""){
			$classTable = TABLE_PREFIX . self::$classTable;
			$gradeTable = TABLE_PREFIX . self::$gradeTable;
	    	// 过滤已删除年级
			$this->db->join($gradeTable, $gradeTable .".id = ". $classTable . ".grade_id" , 'left');
	    	// 查询
			$where = " $gradeTable.`is_delete` = 0 ";
			// 支持‘功能课室’，school_id和campus_id都为空
	    	if(isset($params['schoolId'])) $where .= " AND ($classTable.`school_id`='{$params['schoolId']}' OR $classTable.`school_id`=0)";
	    	if(isset($params['campusId'])) $where .= " AND ($classTable.`campus_id`='{$params['campusId']}' OR $classTable.`campus_id`=0)";
	    	if(isset($params['gradeId'])) $where .= " AND $classTable.`grade_id`='{$params['gradeId']}'";
	    	if(isset($params['classId'])) {
				if(empty($params['classId'])) {
					$where .= ' AND 0 ';
				} else {
					$where .= " AND $classTable.`id` IN ({$params['classId']})";
				}
			}
	    	if(isset($params['classType'])) $where .= " AND $classTable.`class_type`='{$params['classType']}'";
	    	// 删除状态
			if(isset($params['isDelete'])) $where .= " AND $classTable.`is_delete`='{$params['isDelete']}'";
			// // 班主任
			// if(isset($params['classMaster'])) $where .= " AND `class_master`='{$params['classMaster']}'";

			// 年级id列表
			if(isset($params['gradeIdSet'])) {
				$where .= " AND FIND_IN_SET(`grade_id`, '{$params['gradeIdSet']}')";
			}

			// 默认排除已毕业
			if(!isset($params['includeGraduated'])) {
				$where .= " AND $classTable.`is_graduated` = 0";
			}

			// 是否功能课室
			if(isset($params['inFunctional'])) {
				$where .= " AND $classTable.`is_functional` = '{$params['inFunctional']}'";
			}

	    	$this->db->where($where);
	    	// 分页
	    	if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
			// 排序
			if(isset($params['orderBy'])) {
				if(isset($params['orderBy']['order'])) {
					$this->db->order_by("{$classTable}." . $params['orderBy']['order'], $params['orderBy']['sort']);
				} else {
					$this->db->order_by("{$classTable}." .$params['orderBy']);
				}
			} else {
				// 数值越大越靠前
				$this->db->order_by("{$classTable}.sort_level", 'DESC');
				// 默认按班名排序
				$this->db->order_by("{$classTable}.class_name", 'ASC');
			}
	    	// 查询指定字段
			// if(isset($params['field'])) $this->db->select($params['field']);
			$this->db->select("{$classTable}.*");
	    	// 是否只获取条数
	    	if(isset($params['getRowNumber'])) return $this->db->count_all_results(self::$classTable);
	    	return $this->db->get(self::$classTable)->result_array();
	    }
	    // 获取年级，班级列表
	    public function get_grade_class_list($params){
	    	// 条件查询
	    	$where = 1;
			if(isset($params['schoolId'])) $where .= " AND ". self::$classTable. ".school_id = '{$params['schoolId']}'";
			if(isset($params['campusId'])) $where .= " AND ". self::$classTable. ".campus_id = '{$params['campusId']}'";
	    	if(isset($params['classId'])) {
	    		$classId = $params['classId'] ? $params['classId'] : 0;
	    		$where .= " AND ". self::$classTable. ".id IN ($classId)";
	    	}
	    	// 删除状态
			if(isset($params['isDelete'])) $where .= " AND ". self::$classTable .".is_delete='{$params['isDelete']}'";
			// 班级类型，行政班或教学班
			if(isset($params['classType'])) $where .= " AND ". self::$classTable .".class_type='{$params['classType']}'";

			// 默认排除已毕业
			if(!isset($params['includeGraduated'])) {
				$where .= " AND " . self::$classTable . ".`is_graduated` = 0";
				$where .= " AND " . self::$gradeTable . ".`is_graduated` = 0";
			}

			// 按sort排序
			$this->db->order_by(self::$classTable . '.sort_level', 'DESC');
	    	$this->db->where($where);
	    	// 分页
	    	if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
	    	// 搜索字段
	    	$selectField = self::$classTable. ".id, `class_name`, `grade_name`";
	    	$this->db->select($selectField);
	    	// 连表查询
	    	$this->db->from(self::$classTable);
			$this->db->join(self::$gradeTable, self::$gradeTable.".id = ".self::$classTable. ".grade_id" , 'left');
			// 按sort排序
			$this->db->order_by(self::$gradeTable . '.sort_level', 'DESC');
	    	$result = $this->db->get();
	    	// 返回结果
	    	if(isset($params['getRowNumber']) && $params['getRowNumber']) return $result->num_rows();
	    	if(isset($params['getLevelData']) && $params['getLevelData']) {
	    		// 获取年级班级上下级结构
	    		$classList = $result->result_array();
	    		$gradeArray = array();
	    		foreach($classList as $class) {
		            if(!isset($gradeArray[$class['grade_name']])) $gradeArray[$class['grade_name']]['class'] = array();
		            $gradeArray[$class['grade_name']]['class'][] = $class;
		        }
		        return $gradeArray;
	    	}
	    	return $result->result_array();
		}


	    // 获取课室详情
	    public function get_classroom_info($classroomId, $excludeDelete=0){
			$where = "`id`='{$classroomId}'";
			if($excludeDelete) {
				$where .= " AND `is_delete`=0";
			}
			$classRoomInfo = $this->db->where($where)->get(self::$classroomTable)->row_array();
			// var_export($classRoomInfo);exit;
	    	return $classRoomInfo;
		}
	    // 通过名称获取课室详情
	    public function get_classroom_info_by_name($roomName, $schoolId, $campusId){
	    	$this->db->where(array(
				'classroom_name' => $roomName,
				'school_id' => $schoolId,
				'campus_id' => $campusId,
				'is_delete' => 0
			));
	    	$classRoomInfo = $this->db->get(self::$classroomTable)->row_array();
	    	return $classRoomInfo;
		}
	    // 获取课室列表
	    public function get_classroom_list($params = ""){
	    	// 条件查询
	    	$where = 1;
	    	if(isset($params['schoolId'])) $where .= " AND ". self::$classroomTable. ".school_id = '{$params['schoolId']}'";
	    	if(isset($params['campusId'])) $where .= " AND ". self::$classroomTable. ".campus_id = '{$params['campusId']}'";

			// 筛选
			if(isset($params['keyword'])) $where .= " AND ". self::$classroomTable. ".`classroom_name` LIKE '%{$params['keyword']}%'";

	    	// 删除状态
	    	if(isset($params['isDelete'])) $where .= " AND ". self::$classroomTable .".is_delete='{$params['isDelete']}'";
			$this->db->where($where);
			// 排序
			if(isset($params['orderBy'])) {
				$this->db->order_by($params['orderBy']['order'], $params['orderBy']['sort']);
			} else {
				// 默认按更新时间
				$this->db->order_by('updatetime', 'DESC');
			}
	    	// 分页
	    	if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
	    	// 搜索字段
			// $selectField = self::$classroomTable. ".*, `class_name`";
			$selectField = self::$classroomTable. ".*";
	    	$this->db->select($selectField);
	    	// 连表查询
	    	$this->db->from(self::$classroomTable);
	    	// $this->db->join(self::$classTable, self::$classroomTable.".id = ".self::$classTable. ".classroom_id" , 'inner');
	    	$result = $this->db->get();
	    	// 返回结果
	    	// 是否只获取条数
			if(isset($params['getRowNumber'])){
				return count($result->result_array());
			}
			$arr =  $result->result_array();
			$arr = array_map(function($value) {
				$classTableName = TABLE_PREFIX . self::$classTable;
				$gradeTableName = TABLE_PREFIX . self::$gradeTable;
				$sql = "SELECT {$classTableName}.`class_name` FROM {$classTableName} LEFT JOIN {$gradeTableName} ON {$gradeTableName}.id = {$classTableName}.grade_id
				  WHERE {$classTableName}.`classroom_id` = {$value['id']} AND {$classTableName}.`is_delete` = 0 AND {$gradeTableName}.`is_delete` = 0 AND {$gradeTableName}.`is_graduated` = 0 AND {$classTableName}.`is_graduated` = 0 ";
				$query = $this->db->query($sql);
				$classNameArr = [];
				foreach ($query->result() as $row)
				{
					$classNameArr[] = $row->class_name;
				}		
				$classNameStr = implode('，', $classNameArr);
				$length = mb_strlen($classNameStr, WEB_CHARSET);
				if (60 < $length) {
					$classNameStr = mb_substr($classNameStr, 0, 60, WEB_CHARSET);
					$classNameStr .= '......';
				}
				$value['class_name'] = $classNameStr;
				return $value;
			}, $arr);
			return $arr;
	    }
	    // 清空课室绑定的班级
	    public function clear_classroom_class($classroomId){
	    	$update['classroom_id'] = 0;
	    	$this->db->update(ClassesModel::$classTable, $update, "`classroom_id`='{$classroomId}'");
		}

		// 获取学校课程模板（兼容年级不同作息）
		public function get_school_schedule_row($params = ""){
			if(isset($params['schoolId']) && isset($params['campusId']) && isset($params['gradeId'])) {
				if($this->check_is_only_one_schedule($params['schoolId'], $params['campusId']) == false) {
					// 年级不同作息
					if(isset($params['week'])) {
						$week = $params['week'];
					} else {
						// 获取当天的年级作息
						// 转换date('w')获取的值为作息时间的week
						$this->load->helper('schedule');
						$week = transform__to_schedule_week(date('w'));
					}
					$gradeSchedule = $this->classesModel->get_one_daily_grade_schdedule_by_week($params['schoolId'], $params['campusId'], $params['gradeId'], $week);
					// var_export($gradeSchedule);exit;
					if($gradeSchedule) {
						return $gradeSchedule;
					}
				}
			}
			
			// var_export($gradeSchedule);exit;

			$where = "1 AND 1";
			if(isset($params['schoolId'])) $where .= " AND `school_id`='{$params['schoolId']}'";
			// 添加过滤校区
			if(isset($params['campusId'])) $where .= " AND `campus_id`='{$params['campusId']}'";
			// 是否本周
			if(isset($params['isThisWeek'])) $where .= " AND `schedule_this_week`='{$params['isThisWeek']}'";
			// 过滤软删除
			if(isset($params['isDelete'])) $where .= " AND `is_delete`='{$params['isDelete']}'";
			$this->db->where($where);

			return $this->db->get(self::$scheduleTable)->row_array();
		}
	    // 获取学校课程模板
	    public function get_school_schedule($params = ""){
	    	$where = "1 AND 1";
			if(isset($params['schoolId'])) $where .= " AND `school_id`='{$params['schoolId']}'";
			// 添加过滤校区
			if(isset($params['campusId'])) $where .= " AND `campus_id`='{$params['campusId']}'";
			// 是否本周
			if(isset($params['isThisWeek'])) $where .= " AND `schedule_this_week`='{$params['isThisWeek']}'";
			// 年级id
			if(isset($params['gradeId'])) $where .= " AND `grade_id`='{$params['gradeId']}'";
			// 过滤软删除
			if(isset($params['isDelete'])) $where .= " AND `is_delete`='{$params['isDelete']}'";
			$this->db->where($where);
			// 排序
			if(isset($params['orderBy'])) {
				$this->db->order_by($params['orderBy']);
			} else {
				// 默认按id倒序
				$this->db->order_by('id', 'DESC');
			}
	    	return $this->db->get(self::$scheduleTable)->result_array();
		}
		// 获取班级课表详情
		public function get_school_schedule_info($id) {
			$this->db->where('id', $id);
			return $this->db->get(self::$scheduleTable)->row_array();
		}
	    // 获取校区本周课程表设置
	    public function get_campus_schedule($campusId) {
	    	$where = "`campus_id`='{$campusId}' AND `schedule_this_week` = 1";
	    	$schedule = $this->db->where($where)->get(self::$scheduleTable)->row_array();
	    	return $schedule;
	    }
	    // 获取班级课表科目
	    public function get_school_schedule_lesson($params = ""){
			$scheduleLessonTb = self::$scheduleLessonTable;
			$classTb = self::$classTable;
			$classroomTb = self::$classroomTable;
			$this->db->join($classTb, "{$classTb}.id = {$scheduleLessonTb}.class_id", 'left');
			$this->db->join($classroomTb, "{$classroomTb}.id = {$scheduleLessonTb}.schedule_room_id", 'left');
			// 过滤班级和课室已删除
			$where = "1 AND {$scheduleLessonTb}.`is_delete` = 0 AND {$classTb}.`is_delete`=0 AND {$classroomTb}.`is_delete`=0";
	    	if(isset($params['schoolId'])) $where .= " AND {$scheduleLessonTb}.`school_id`='{$params['schoolId']}'";
	    	if(isset($params['scheduleId'])) $where .= " AND {$scheduleLessonTb}.`schedule_id`='{$params['scheduleId']}'";
	    	if(isset($params['classId'])) $where .= " AND {$scheduleLessonTb}.`class_id`='{$params['classId']}'";
	    	if(isset($params['classSet'])) $where .= " AND {$scheduleLessonTb}.`class_id` IN ({$params['classSet']})";
	    	if(isset($params['classroom'])) $where .= " AND {$scheduleLessonTb}.`schedule_room_id`='{$params['classroom']}'";
	    	if(isset($params['weekIndex'])) $where .= " AND {$scheduleLessonTb}.`schedule_week_index`='{$params['weekIndex']}'";
			$this->db->where($where);
			$this->db->select("{$scheduleLessonTb}.*");
	    	// 排序
	    	$orderBy = "{$scheduleLessonTb}.`schedule_week_index` ASC, {$scheduleLessonTb}.`schedule_class_index` ASC";
	    	return $this->db->order_by($orderBy)->get(self::$scheduleLessonTable)->result_array();
		}
	    // 获取班级课表科目，包含课室id为0的情况
	    public function get_school_schedule_lesson_for_sync($params = ""){
			$this->db->where('school_id', $params['schoolId']);
	    	return $this->db->get(self::$scheduleLessonTable)->result_array();
	    }
		 // 获取班级课表科目
		 public function get_school_schedule_lesson_fast($params = ""){
			if(isset($params['schoolId'])) $this->db->where('school_id', $params['schoolId']);
			if(isset($params['campusId'])) $this->db->where('campus_id', $params['campusId']);
			if(isset($params['isDelete'])) $this->db->where('is_delete', $params['isDelete']);
			// 小于等于第几节
			if(isset($params['leClassIndex'])) $this->db->where('schedule_class_index <=', $params['leClassIndex']);
			// 大于第几节
			if(isset($params['gtClassIndex'])) $this->db->where('schedule_class_index >', $params['gtClassIndex']);
	    	return $this->db->get(self::$scheduleLessonTable)->result_array();
		}
	    // 获取指定时间的课程
	    public function get_schedule_lesson_info($scheduleId, $weekIndex, $classIndex, $classRoom){
			$scheduleLessonTb = self::$scheduleLessonTable;
			$classTb = self::$classTable;
			$classroomTb = self::$classroomTable;
			$this->db->join($classTb, "{$classTb}.id = {$scheduleLessonTb}.class_id", 'left');
			$this->db->join($classroomTb, "{$classroomTb}.id = {$scheduleLessonTb}.schedule_room_id", 'left');

			// $where = "`schedule_id`='{$scheduleId}' AND `schedule_week_index`='{$weekIndex}' AND `schedule_class_index`='{$classIndex}' AND `schedule_room_id`='{$classRoom}' 
			// AND {$scheduleLessonTb}.`is_delete` = 0 AND {$classTb}.`is_delete` = 0 AND {$classroomTb}.`is_delete` = 0";
			// 排除毕业班课程
			$where = "`schedule_id`='{$scheduleId}' AND `schedule_week_index`='{$weekIndex}' AND `schedule_class_index`='{$classIndex}' AND `schedule_room_id`='{$classRoom}' 
			AND {$scheduleLessonTb}.`is_delete` = 0 AND {$classTb}.`is_delete` = 0 AND {$classTb}.`is_graduated` = 0 AND {$classroomTb}.`is_delete` = 0";
			$this->db->select("{$scheduleLessonTb}.*");
			$scheduleLesson = $this->db->where($where)->get(self::$scheduleLessonTable)->row_array();
	    	return $scheduleLesson;
		}

		// 获取指定时间的课程（年级不同作息，不需要传scheduleid）
		public function get_schedule_lesson_info_for_grade($weekIndex, $classIndex, $classRoom){
			$scheduleLessonTb = self::$scheduleLessonTable;
			$classTb = self::$classTable;
			$classroomTb = self::$classroomTable;
			$this->db->join($classTb, "{$classTb}.id = {$scheduleLessonTb}.class_id", 'left');
			$this->db->join($classroomTb, "{$classroomTb}.id = {$scheduleLessonTb}.schedule_room_id", 'left');
			// 排除毕业班课程
			$where = "`schedule_week_index`='{$weekIndex}' AND `schedule_class_index`='{$classIndex}' AND `schedule_room_id`='{$classRoom}' 
			AND {$scheduleLessonTb}.`is_delete` = 0 AND {$classTb}.`is_delete` = 0 AND {$classTb}.`is_graduated` = 0 AND {$classroomTb}.`is_delete` = 0";
			$this->db->select("{$scheduleLessonTb}.*");
			$scheduleLesson = $this->db->where($where)->get(self::$scheduleLessonTable)->row_array();
			return $scheduleLesson;
		}

		// 用于导入课表数据时，判断是否已有该条数据
		public function get_schedule_lesson_info_for_import($scheduleId, $weekIndex, $classIndex, $classId){
			// 不用过滤is_delete
			$where = "`schedule_id`='{$scheduleId}' AND `schedule_week_index`='{$weekIndex}' AND `schedule_class_index`='{$classIndex}' AND `class_id`='{$classId}' ";
			$scheduleLesson = $this->db->where($where)->get(self::$scheduleLessonTable)->row_array();
			return $scheduleLesson;
		}
	    // 根据id，获取课程信息
	    public function get_sch_lesson($scheduleId){
	    	$where = "`id`='{$scheduleId}'";
	    	$scheduleLesson = $this->db->where($where)->get(self::$scheduleLessonTable)->row_array();
	    	return $scheduleLesson;
		}
		
		// 根据班级id，获取课程模板
		public function get_schedule_by_class($schoolId, $gradeId) {
			// $scheduleTb = self::$scheduleTable;
			// $lessonTb = self::$scheduleLessonTable;
			// $this->db->join($lessonTb, "{$scheduleTb}.id = {$lessonTb}.schedule_id");
			// $this->db->where("{$lessonTb}.is_delete", 0);
			// $this->db->where("{$lessonTb}.class_id", $classId);
			// $this->db->where("{$scheduleTb}.schedule_this_week", $isThisWeek);
			// $this->db->select("{$scheduleTb}.*");
			// return $this->db->get($scheduleTb)->row_array();

			$this->db->where("is_delete", 0);
			$this->db->where("school_id", $schoolId);
			$this->db->where("grade_id", $gradeId);
			$this->db->where("schedule_this_week", 1);
			return $this->db->get(self::$scheduleTable)->row_array();
		}

		// 根据课室id，获取课程模板
		public function get_schedule_by_classroom($classroomId) {
			$scheduleTb = self::$scheduleTable;
			$lessonTb = self::$scheduleLessonTable;
			$this->db->join($lessonTb, "{$scheduleTb}.id = {$lessonTb}.schedule_id");
			$this->db->where("{$lessonTb}.is_delete", 0);
			$this->db->where("{$lessonTb}.schedule_room_id", $classroomId);
			$this->db->where("{$scheduleTb}.schedule_this_week", 1);
			$this->db->select("{$scheduleTb}.*");
			return $this->db->get($scheduleTb)->row_array();
		}

		// 判断是否年级不同作息（存在一个grade_id不等于0的即是）
		public function check_is_only_one_schedule($schoolId, $campusId) {
			$this->db->where('school_id', $schoolId);
			$this->db->where('campus_id', $campusId);
			$this->db->where('grade_id !=', 0);
			$this->db->where('is_delete', 0);
	    	$gradeSchedule = $this->db->get(self::$scheduleTable)->row_array();
			if($gradeSchedule) {
				return false;
			} else {
				return true;
			}
		}

		// 获取各年级每天的作息时间
		public function get_daily_grade_schdedule($schoolId, $campusId) {
			// 年级不同作息
			$this->db->where('school_id', $schoolId);
			$this->db->where('campus_id', $campusId);
			$this->db->where('is_delete', 0);
	    	$gradeScheduleList = $this->db->get(self::$scheduleTable)->result_array();
			$tmpScheduleArr = array();
			foreach($gradeScheduleList as $gradeSchedule) {
				$weekArr = explode(',', $gradeSchedule['week']);
				foreach($weekArr as $week) {
					// 拼键值
					$index = $schoolId . '_' . $campusId . '_' . $gradeSchedule['grade_id'] . '_' . $week;
					$tmpScheduleArr[$index] = $gradeSchedule; 
				}
			}
			return $tmpScheduleArr;
		}

		// 获取某个年级每天的作息时间
		public function get_one_daily_grade_schdedule($schoolId, $campusId, $gradeId) {
			// 年级不同作息
			$this->db->where('school_id', $schoolId);
			$this->db->where('campus_id', $campusId);
			$this->db->where('grade_id', $gradeId);
			$this->db->where('is_delete', 0);
	    	$gradeScheduleList = $this->db->get(self::$scheduleTable)->result_array();
			$tmpScheduleArr = array();
			foreach($gradeScheduleList as $gradeSchedule) {
				$weekArr = explode(',', $gradeSchedule['week']);
				foreach($weekArr as $week) {
					// 以w星期几作为键值
					$index = $week;
					$tmpScheduleArr[$index] = $gradeSchedule; 
				}
			}
			// 排序
			ksort($tmpScheduleArr);
			return $tmpScheduleArr;
		}

		// 获取某个年级某天的作息时间
		public function get_one_daily_grade_schdedule_by_week($schoolId, $campusId, $gradeId, $week) {
			// 年级不同作息
			$this->db->where('school_id', $schoolId);
			$this->db->where('campus_id', $campusId);
			$this->db->where('grade_id', $gradeId);
			// $this->db->where_in('week', $week);
			$this->db->where("FIND_IN_SET('{$week}', `week`)");
			$this->db->where('is_delete', 0);
	    	return $this->db->get(self::$scheduleTable)->row_array();
		}

	    // 获取班牌列表
	    public function get_brand_list($params = ""){
			$brandTableName = self::$brandTable;
			$classTableName = self::$classTable;
			$gradeTableName = self::$gradeTable;
	    	// 查询
	    	$where = "1 AND 1";
	    	if(isset($params['schoolId'])) $where .= " AND  {$brandTableName}.`school_id`='{$params['schoolId']}'";
	    	if(isset($params['campusId'])) $where .= " AND {$brandTableName}.`campus_id`='{$params['campusId']}'";
	    	if(isset($params['classIdSet']) && !empty($params['classIdSet'])) $where .= " AND {$brandTableName}.`class_id` IN ({$params['classIdSet']})";
			if(isset($params['online'])) $where .= " AND {$brandTableName}.`is_online` = '{$params['online']}'";
			
			// 过滤已删除的班级
			$this->db->select('*,' . self::$brandTable . '.id AS id,' . self::$brandTable . '.campus_id AS campus_id');
			$this->db->join(self::$classTable, self::$brandTable . '.class_id = ' . self::$classTable . '.id');
			$this->db->join(self::$gradeTable, self::$classTable . '.grade_id = ' . self::$gradeTable . '.id');
			// 删除年级，不删除班牌，班级名留空
			// $where .= " AND {$classTableName}.`id` IS NOT NULL AND {$classTableName}.`is_delete` = 0 AND {$gradeTableName}.`is_delete` = 0 ";

	    	$this->db->where($where);
	    	// 分页
	    	if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
	    	// 排序
			$orderBy = (isset($params['orderBy']) && !isset($params['orderByClassName'])) ? $params['orderBy'] : self::$brandTable . '.`online_time` DESC';
			// 按班级名称和排序值排序
			if(isset($params['orderByClassName'])) {
				$orderBy = self::$classTable . '.sort_level DESC,' . self::$classTable . '.class_name ASC';
			}
			// 按班级名称和在线状态排序
			if(isset($params['orderByClassNameAndState'])) {
				$orderBy = self::$brandTable . '.`is_online` DESC,' . self::$classTable . '.sort_level DESC,' .self::$classTable . '.`class_name` ASC';
			}
	    	// 是否只获取条数
	    	if(isset($params['getRowNumber'])) return $this->db->count_all_results(self::$brandTable);
	    	return $this->db->order_by($orderBy)->get(self::$brandTable)->result_array();
	    }
		// 获取班牌列表
		public function get_brand_list_left_join_class($params = ""){
			$brandTableName = self::$brandTable;
			$classTableName = self::$classTable;
			$gradeTableName = self::$gradeTable;
			// 查询
			$where = "1 AND 1";
			if(isset($params['schoolId'])) $where .= " AND  {$brandTableName}.`school_id`='{$params['schoolId']}'";
			if(isset($params['campusId'])) $where .= " AND {$brandTableName}.`campus_id`='{$params['campusId']}'";
			if(isset($params['classIdSet']) && !empty($params['classIdSet'])) $where .= " AND {$brandTableName}.`class_id` IN ({$params['classIdSet']})";
			if(isset($params['online'])) $where .= " AND {$brandTableName}.`is_online` = '{$params['online']}'";
			
			// 过滤已删除的班级
			$this->db->select('*,' . self::$brandTable . '.id AS id,' . self::$brandTable . '.campus_id AS campus_id');

			// $this->db->join(self::$classTable, self::$brandTable . '.class_id = ' . self::$classTable . '.id');
			// $this->db->join(self::$gradeTable, self::$classTable . '.grade_id = ' . self::$gradeTable . '.id');
			// 使用左连接，查的到class_id=0的数据
			$this->db->join(self::$classTable, self::$brandTable . '.class_id = ' . self::$classTable . '.id', 'left');
			$this->db->join(self::$gradeTable, self::$classTable . '.grade_id = ' . self::$gradeTable . '.id', 'left');

			// 删除年级，不删除班牌，班级名留空
			// $where .= " AND {$classTableName}.`id` IS NOT NULL AND {$classTableName}.`is_delete` = 0 AND {$gradeTableName}.`is_delete` = 0 ";

			$this->db->where($where);
			// 分页
			if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
			// 排序
			$orderBy = (isset($params['orderBy']) && !isset($params['orderByClassName'])) ? $params['orderBy'] : '`online_time` DESC';
			// 按班级名称和排序值排序
			if(isset($params['orderByClassName'])) {
				$orderBy = self::$classTable . '.sort_level DESC,' . self::$classTable . '.class_name ASC';
			}
			// 按班级名称和在线状态排序
			if(isset($params['orderByClassNameAndState'])) {
				$orderBy = self::$brandTable . '.`is_online` DESC,' . self::$classTable . '.sort_level DESC,' .self::$classTable . '.`class_name` ASC';
			}
			// 是否只获取条数
			if(isset($params['getRowNumber'])) return $this->db->count_all_results(self::$brandTable);
			return $this->db->order_by($orderBy)->get(self::$brandTable)->result_array();
		}
		// 获取班牌列表
		public function get_brand_list_not_join($params = ""){
			$brandTableName = self::$brandTable;
			// 查询
			$where = "1 AND 1";
			if(isset($params['schoolId'])) $where .= " AND  {$brandTableName}.`school_id`='{$params['schoolId']}'";
			if(isset($params['campusId'])) $where .= " AND {$brandTableName}.`campus_id`='{$params['campusId']}'";
			if(isset($params['online'])) $where .= " AND {$brandTableName}.`is_online` = '{$params['online']}'";
			$this->db->where($where);
			// 分页
			if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
			// 排序
			$orderBy = (isset($params['orderBy']) && !isset($params['orderByClassName'])) ? $params['orderBy'] : '`online_time` DESC';
			// 按班级名称和排序值排序
			if(isset($params['orderByClassName'])) {
				$orderBy = self::$classTable . '.sort_level DESC,' . self::$classTable . '.class_name ASC';
			}
			// 按班级名称和在线状态排序
			if(isset($params['orderByClassNameAndState'])) {
				$orderBy = self::$brandTable . '.`is_online` DESC,' . self::$classTable . '.sort_level DESC,' .self::$classTable . '.`class_name` ASC';
			}
			// 是否只获取条数
			if(isset($params['getRowNumber'])) return $this->db->count_all_results(self::$brandTable);
			return $this->db->order_by($orderBy)->get(self::$brandTable)->result_array();
		}
	    // 获取班牌信息
	    public function get_brand_info($brandId) {
	    	$where = "`id`='{$brandId}'";
	    	$brandInfo = $this->db->where($where)->get(self::$brandTable)->row_array();
	    	return $brandInfo;
		}
	    // 获取班牌信息，通过class_id
	    public function get_brand_info_by_class($classId) {
	    	$where = "`class_id`='{$classId}'";
	    	$brandInfo = $this->db->where($where)->get(self::$brandTable)->row_array();
	    	return $brandInfo;
		}

	    // // 获取当天签到信息
	    // public function get_today_sign($studentId, $classId) {
	    // 	$today = date("Y-m-d");
	    // 	$where = "`class_id`='{$classId}' AND `student_id`='{$studentId}' AND `sign_date`='{$today}'";
	    // 	$signList = $this->db->where($where)->get(self::$signTable)->result_array();
	    // 	return $signList;
	    // }

	    // 获取签到信息
	    public function get_sign_info($signId) {
	    	$where = "`id`='{$signId}'";
	    	$signInfo = $this->db->where($where)->get(self::$signTable)->row_array();
	    	return $signInfo;
		}
		
		// 通过时间获取签到信息
		public function get_sign_info_by_createtime($createtime, $studentId, $courseId = 0, $userType = USER_TYPE_STUDENT)
		{
			$beginTime = date('Y-m-d H:i:s', strtotime($createtime) - 600);
			$endTime = date('Y-m-d H:i:s', strtotime($createtime) + 600);
			$this->db->where('student_id', $studentId);
			$this->db->where('user_type', $userType);

			if($courseId != 0) {
				$this->db->where('course_id', $courseId);
			}

			$this->db->where('createtime >=', $beginTime);
			$this->db->where('createtime <=', $endTime);
			// 按创建时间倒序
			$this->db->order_by('createtime', 'DESC');
	    	$signInfo = $this->db->get(self::$signTable)->row_array();
	    	return $signInfo;
		}

		// 通过时间获取签到信息
		public function get_sign_info_by_createtime_gate($createtime, $studentId,  $userType = USER_TYPE_STUDENT)
		{
			$beginTime = date('Y-m-d H:i:s', strtotime($createtime) - 600);
			$endTime = date('Y-m-d H:i:s', strtotime($createtime) + 600);
			$this->db->where('student_id', $studentId);
			$this->db->where('user_type', $userType);

			$this->db->where('createtime >=', $beginTime);
			$this->db->where('createtime <=', $endTime);
			$this->db->where('sign_type', SIGN_TYPE_GATE);
			// 按创建时间倒序
			$this->db->order_by('createtime', 'DESC');
	    	$signInfo = $this->db->get(self::$signTable)->row_array();
	    	return $signInfo;
		}

		// 获取5分钟内的签到记录（一体机）
		public function get_sign_info_by_createtime_aio($createtime, $studentId, $userType = USER_TYPE_STUDENT)
		{
			$beginTime = date('Y-m-d H:i:s', strtotime($createtime) - 300);
			$this->db->where('student_id', $studentId);
			$this->db->where('user_type', $userType);

			$this->db->where('createtime >=', $beginTime);
			$this->db->where('sign_type', SIGN_TYPE_AIO);
			// 按创建时间倒序
			$this->db->order_by('createtime', 'DESC');
	    	$signInfo = $this->db->get(self::$signTable)->row_array();
	    	return $signInfo;
		}

		// // 通过时间获取签到信息
		// public function get_sign_info_by_sign_date($signdate, $studentId, $courseId)
		// {
		// 	$this->db->where('student_id', $studentId);
		// 	$this->db->where('course_id', $courseId);
		// 	$this->db->where('sign_date', $signdate);
	    // 	$signInfo = $this->db->get(self::$signTable)->row_array();
	    // 	return $signInfo;
		// }
	    // 获取签到列表(只用于推送考勤)
	    public function get_sign_data($params){
	    	// 查询
	    	$where = "1 AND 1";
	    	if(isset($params['schoolId'])) $where .= " AND `school_id` IN ({$params['schoolId']})";
	    	if(isset($params['isSubmit'])) $where .= " AND `sign_is_submit` = '{$params['isSubmit']}'";
	    	$this->db->where($where);
	    	// 分页
	    	if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
	    	// return $this->db->order_by("id ASC")->get(self::$signTable)->result_array();
	    	return $this->db->get(self::$signTable)->result_array();
		}
		// 获取学段列表
		public function get_grade_period_list(){
			$this->db->where('is_delete', 0);
			return $this->db->get(self::$gradePeriodTable)->result_array();
		}
		// 获取学段信息
		public function get_grade_period_info($periodId){
	    	$where = "`id`='{$periodId}'";
	    	return $this->db->where($where)->get(self::$gradePeriodTable)->row_array();
		}
		// 获取默认年级列表
		public function get_default_grade_list($params){
			$this->db->where('is_delete', 0);
			// 过滤学段
			if(isset($params['periodId'])) {
				$this->db->where('period_id', $params['periodId']);
			}
			// 过滤已毕业
			if(isset($params['excludeGraduated'])) {
				$this->db->where('next_grade_id !=', 0);
			}
			// 获取已毕业
			if(isset($params['isGraduated'])) {
				$this->db->where('next_grade_id =', 0);
			}
			if(isset($params['field'])) {
				$this->db->select($params['field']);
			}
			return $this->db->get(self::$defaultGradeTable)->result_array();
		}
		// 获取默认年级信息
		public function get_default_grade_info($defaultGradeId){
	    	$where = "`id`='{$defaultGradeId}'";
	    	$gradeInfo = $this->db->where($where)->get(self::$defaultGradeTable)->row_array();
	    	return $gradeInfo;
		}
		// 通过名称获取默认年级信息
		public function get_default_grade_info_by_name($defaultGradeName){
	    	$where = "`name`='{$defaultGradeName}'";
	    	$gradeInfo = $this->db->where($where)->get(self::$defaultGradeTable)->row_array();
	    	return $gradeInfo;
		}
		// 获取默认年级对应的年级信息
		public function get_grade_info_by_default_grade($defaultGradeId, $schoolId) {
			$where = "`is_delete`=0";
			$where .= " AND `default_grade_id`='{$defaultGradeId}'";
			$where .= " AND `school_id` = '{$schoolId}'";
	    	$gradeInfo = $this->db->where($where)->get(self::$gradeTable)->row_array();
	    	return $gradeInfo;
		}
		//检查是否存在
		public function check_class_name_exists($schoolId, $campusId, $name, $id = 0){
			$this->db->where("class_name", $name);
			$this->db->where("school_id", $schoolId);
			$this->db->where("campus_id", $campusId);
			$this->db->where('is_delete', 0);

			// 过滤已毕业
			$this->db->where('is_graduated', 0);

			if($id){
				$this->db->where("id !=", $id);
			}
			return $this->db->get(self::$classTable)->row_array();
		}
		//检查是否存在
		public function check_classroom_name_exists($schoolId, $name, $id = 0){
			$this->db->where("classroom_name", $name);
			$this->db->where("school_id", $schoolId);
			$this->db->where('is_delete', 0);
			if($id){
				$this->db->where("id !=", $id);
			}
			return $this->db->get(self::$classroomTable)->row_array();
		}
		// 查询mac地址
		public function check_brand_mac($macAddress){
			$this->db->where("mac_address", $macAddress);
			return $this->db->get(self::$brandCdkeyTable)->row_array();
		}

		// 获取某班每周的上课天数，0-6代表周日到周一（增加节数参数）
		public function get_class_schedule_week_arr($classInfo, $scheduleClassIndexSet = 0) {
			// 获取学校作息
			$scheduleParams['schoolId'] = $classInfo['school_id'];
			$scheduleParams['campusId'] = $classInfo['campus_id'];
			// 只取本周的
			$scheduleParams['isThisWeek'] = 1;
			// 过滤已删除
			$scheduleParams['isDelete'] = 0;
			$scheduleList = $this->classesModel->get_school_schedule($scheduleParams);
			// 判断是否年级不同作息（存在一个grade_id不等于0的即是）
			$onlyOneSchedule = $this->classesModel->check_is_only_one_schedule($classInfo['school_id'], $classInfo['campus_id']);
			if(!$onlyOneSchedule) {
				// 年级作息的天数和节数相同，取本年级的星期一作息即可
				$mondayGradeSchedule = $this->classesModel->get_one_daily_grade_schdedule_by_week($classInfo['school_id'], $classInfo['campus_id'], $classInfo['grade_id'], 1);
				if($mondayGradeSchedule) {
					$scheduleList = array($mondayGradeSchedule);
				}
			}
			$schedule = $scheduleList[0];
			// 一天几节课
			$classIndexCount = $schedule['schedule_morning_number'] + $schedule['schedule_afternoon_number'] + $schedule['schedule_early_morning_number'] + $schedule['schedule_night_number'];

			// 获取班级上课天数
			$scheduleLessonParams['scheduleId'] = $schedule['id'];
			$scheduleLessonParams['classId'] = $classInfo['id'];
			// 小于等于第几节
			$scheduleLessonParams['leClassIndex'] = $classIndexCount;
			// 节数集合，支持多选，比如：1,2,3,4
			if($scheduleClassIndexSet != 0) {
				$scheduleLessonParams['scheduleClassIndexSet'] = $scheduleClassIndexSet;
			}
			$scheduleLessonParams['isDelete'] = 0;
			$scheduleLessonArr = $this->classesModel->get_school_schedule_lesson($scheduleLessonParams);
			// 使用schedule_week_index作为键值
			$scheduleLessonArr = array_value_to_key($scheduleLessonArr, 'schedule_week_index');
			// 获取周几上课
			$weekArr = array_keys($scheduleLessonArr);
			if($weekArr) {
				if($schedule['schedule_week_days'] == 5) {
					// 去掉周六和周日
					foreach($weekArr as $key => $value) {
						if($value == 6 || $value == 7) {
							unset($weekArr[$key]);
						}
					}
				} else if($schedule['schedule_week_days'] == 6) {
					// 去掉周日
					foreach($weekArr as $key => $value) {
						if($value == 7) {
							unset($weekArr[$key]);
						}
					}
				}
				// 将周日值7改为0
				foreach($weekArr as $key => $value) {
					if($value == 7) {
						$weekArr[$key] = 0;
					}
				}
			}
			return $weekArr;
		}

		// 日期段去掉周几，返回日期数组
		public function get_date_arr_and_filter_week($beginDate, $endDate, $weekArr) {
			// 获取日期区间
			if($beginDate == $endDate) {
				$dateArr = array($beginDate);
			} else {
				$dateArr = getDateFromRange($beginDate, $endDate);
			}
			if(count($weekArr) < 7) {
				foreach($dateArr as $key => $value) {
					// 去掉没上课的日期
					$w = date('w', strtotime($value));
					if(!in_array($w, $weekArr)) {
						unset($dateArr[$key]);
					}
				}
			}
			return $dateArr;
		}

		// 获取某位教师每周的上课天数，0-6代表周日到周一
		public function get_teacher_schedule_week_arr($teacherInfo) {
			// 获取学校作息
			$scheduleParams['schoolId'] = $teacherInfo['school_id'];
			// 只取本周的
			$scheduleParams['isThisWeek'] = 1;
			$scheduleList = $this->classesModel->get_school_schedule($scheduleParams);
			// 获取第一项即可
			$schedule = $scheduleList[0];
			// 获取教师上课天数
			$scheduleLessonParams['teacherId'] = $teacherInfo['id'];
			$scheduleLessonParams['isDelete'] = 0;
			$scheduleLessonArr = $this->classesModel->get_school_schedule_lesson($scheduleLessonParams);
			// 使用schedule_week_index作为键值
			$scheduleLessonArr = array_value_to_key($scheduleLessonArr, 'schedule_week_index');
			// 获取周几上课
			$weekArr = array_keys($scheduleLessonArr);
			if($weekArr) {
				if($schedule['schedule_week_days'] == 5) {
					// 去掉周六和周日
					foreach($weekArr as $key => $value) {
						if($value == 6 || $value == 7) {
							unset($weekArr[$key]);
						}
					}
				} else if($schedule['schedule_week_days'] == 6) {
					// 去掉周日
					foreach($weekArr as $key => $value) {
						if($value == 7) {
							unset($weekArr[$key]);
						}
					}
				}
				// 将周日值7改为0
				foreach($weekArr as $key => $value) {
					if($value == 7) {
						$weekArr[$key] = 0;
					}
				}
			}
		
			return $weekArr;
		}

	}